﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

using Microsoft.ApplicationBlocks.Data;

using YouHP.IDAL;
using YouHP.Model;
using YouHP.Utility;
using System.Data.SqlClient;

namespace YouHP.SqlServerDAL
{
    public class PatientPrescriptionDAL : IPatientPrescription
    {

        #region IPatientPrescription Members

        public List<PrescriptionHistoryInfo> GetUsingPrescriptionList(int prId)
        {
            string condStr = string.Format(@" [PatientCurrentPrescription].[PRId]={0} AND [PatientCurrentPrescription].[IsUsing]=1", prId);

            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr, Common.PatientPrescriptionSP_GetPatientCurrentPrescription, condStr)
                as IDataReader;
            return CBO.FillCollection<PrescriptionHistoryInfo>(reader);
        }

        public List<PrescriptionHistoryInfo> GetStoppedPrescriptionList(int prId)
        {
            string condStr = string.Format(@" [PatientCurrentPrescription].[PRId]={0} AND [PatientCurrentPrescription].[IsUsing]=0", prId);

            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr, Common.PatientPrescriptionSP_GetPatientCurrentPrescription, condStr)
                as IDataReader;
            return CBO.FillCollection<PrescriptionHistoryInfo>(reader);
        }

        public List<PrescriptionHistoryInfo> GetPrescriptionHistoryList(int pageSize, int pageIndex, int prId, int prescriptionId, out int count)
        {
            string condStr = string.Format(@" [PatientPrescriptionHistory].[PrId]={0} AND [PatientPrescriptionHistory].[PrescriptionId]={1}", prId, prescriptionId);
            string orderStr = string.Format(@" [PatientPrescriptionHistory].[Id]");

            DataSet ds = SqlHelper.ExecuteDataset(Common.ConnStr, Common.PatientPrescriptionSP_GetPatientPrescriptionHistoryList, condStr, orderStr);

            IDataReader dr = ds.Tables[0].CreateDataReader();
            if (dr != null && dr.Read())
            {
                count = dr.GetInt32(0);
                dr.Close();
            }
            else
            {
                count = 0;
            }

            dr = ds.Tables[1].CreateDataReader();

            return CBO.FillCollection<PrescriptionHistoryInfo>(dr);
        }

        public List<PrescriptionHistoryInfo> GetPrescriptionHistoryList(int prId, int prescriptionId)
        {
            string condStr = string.Format(@" [PatientPrescriptionHistory].[PRId]={0} AND [PatientPrescriptionHistory].[PrescriptionId]={1}",
                prId, prescriptionId);

            //string orderStr = " [PatientPrescriptionHistory].[StartDate] asc ";

            //IDataReader dr = SqlHelper.ExecuteReader(Common.ConnStr, Common.PatientPrescriptionSP_GetPatientPrescriptionHistory, condStr, orderStr);
            IDataReader dr = SqlHelper.ExecuteReader(Common.ConnStr, Common.PatientPrescriptionSP_GetPatientPrescriptionHistory, condStr);

            return CBO.FillCollection<PrescriptionHistoryInfo>(dr);
        }

        public List<PrescriptionHistoryInfo> GetPrescripitonHistoryListByPid(int prescriptionId)
        {
            string condStr = string.Format(@"[PatientPrescriptionHistory].[PrescriptionId]={0}",prescriptionId);

            IDataReader dr = SqlHelper.ExecuteReader(Common.ConnStr, Common.PatientPrescriptionSP_GetPatientPrescriptionHistory, condStr);

            return CBO.FillCollection<PrescriptionHistoryInfo>(dr);
        }

        public bool AddPrescriptionHistory(PrescriptionHistoryInfo info)
        {
            bool retValue = false;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                // 添加历史记录
                info.PrescriptionHistoryId = AddPrescriptionHistory(ts, info);

                if (info.State == Constants.CURRENT_USING)
                {
                    info.IsUsing = 1;
                }
                else if (info.State == Constants.STOPED_USE)
                {
                    info.IsUsing = 0;
                }

                if (CheckExsitCurrentPrescription(ts, info.PRId, info.PrescriptionId))
                {
                    PrescriptionHistoryInfo curInfo = GetCurrentPrescription(info.PRId, info.PrescriptionId);
                    if (curInfo.Id <= 0)
                    {
                        ts.Rollback();
                        conn.Close();
                        return false;
                    }
                    info.Id = curInfo.Id;

                    if (info.State == Constants.STOPED_USE)
                    {
                        info.PrescriptionHistoryId = curInfo.PrescriptionHistoryId;
                    }

                    int rowsAffected = UpdateCurrentPrescription(ts, info);
                    if (rowsAffected <= 0)
                    {
                        ts.Rollback();
                        conn.Close();
                        return false;
                    }

                    // Update the start history record, set the end time correctly.
                    if (info.State == Constants.STOPED_USE)
                    {
                        PrescriptionHistoryInfo startInfo = GetPrescriptionHistory(info.PRId, curInfo.PrescriptionHistoryId);
                        if (startInfo.Id <= 0)
                        {
                            ts.Rollback();
                            conn.Close();
                            return false;
                        }

                        startInfo.EndDate = info.StartDate;
                        UpdatePrescriptionHistory(ts, startInfo);
                    }
                }
                else
                {
                    if (info.State == Constants.START_USE)
                        AddCurrentPrescription(ts, info);
                }


                ts.Commit();
                retValue = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retValue;
        }

        public bool UpdatePrescriptionHistotry(PrescriptionHistoryInfo info)
        {
            bool retVal = false;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                UpdatePrescriptionHistory(ts, info);

                info.PrescriptionHistoryId = info.Id;    //为操作当前记录准备数据

                //判断当前表中是否存在记录，存在则需要更新当前的记录值，否则执行添加
                if (CheckExsitCurrentPrescription(ts, info.PRId, info.PrescriptionId))
                {
                    //执行修改
                    UpdateCurrentPrescription(ts, info);
                }

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        public bool StopPrescriptionHistory(int prId, int prescriptionId, DateTime date)
        {
            bool retValue = false;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                PrescriptionHistoryInfo currentInfo = GetCurrentPrescription(prId, prescriptionId);
                if (currentInfo.Id <= 0)
                {
                    conn.Close();
                    return false;
                }

                // First, add a new record to the history table, get the 'id',
                // Second, update the history entry in current table, set isUsing=0.
                PrescriptionHistoryInfo startInfo = GetPrescriptionHistory(prId, currentInfo.PrescriptionHistoryId);
                if (startInfo.Id <= 0)
                {
                    conn.Close();
                    return false;
                }

                PrescriptionHistoryInfo stopInfo = startInfo.Clone();
                stopInfo.State = Constants.STOPED_USE;
                stopInfo.StartDate = date;
                stopInfo.EndDate = DateTime.Now;
                int stopHistoryId = AddPrescriptionHistory(ts, stopInfo);

                //currentInfo.PrescriptionHistoryId = stopHistoryId;

                if (stopHistoryId <= 0)
                {
                    conn.Close();
                    return false;
                }

                if (CheckExsitCurrentPrescription(ts, prId, prescriptionId))
                {
                    // 不在中药当前表中删除记录，而是标志该中药已经停用
                    currentInfo.IsUsing = 0;
                    UpdateCurrentPrescription(ts, currentInfo);
                }

                ts.Commit();
                retValue = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retValue;
        }

        public bool DelPrescriptionHistory(int prId, int historyId)
        {
            bool retValue = false;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                PrescriptionHistoryInfo info = GetPrescriptionHistory(prId, historyId);
                if (info.Id <= 0)
                {
                    ts.Rollback();
                    conn.Close();
                    return false;
                }

                DelPrescriptionHistory(prId, historyId);

                ts.Commit();
                retValue = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retValue;
        }

        public bool CheckPrescriptionHistory(int prId, int prescriptionId, DateTime startDate)
        {
            bool retVal = false;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                int thId = GetPrescriptionHistory(ts, prId, prescriptionId, startDate);
                if (thId > 0)
                {
                    retVal = true;
                }

                ts.Commit();
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        public PrescriptionHistoryInfo GetCurrentPrescription(int prId, int prescriptionId)
        {
            string condStr = string.Format(@" [PatientCurrentPrescription].[PRId]={0} AND ([PatientCurrentPrescription].[PrescriptionId]={1} OR [PatientCurrentPrescription].[ParentPrescriptionId]={1})",
                prId, prescriptionId);

            IDataReader dr = SqlHelper.ExecuteReader(Common.ConnStr, Common.PatientPrescriptionSP_GetPatientCurrentPrescription, condStr)
                as IDataReader;

            return CBO.FillObject<PrescriptionHistoryInfo>(dr);
        }

        public PrescriptionHistoryInfo GetPrescriptionHistory(int prId, int historyId)
        {
            string condStr = string.Format(@" [PatientPrescriptionHistory].[PRId]={0} AND [PatientPrescriptionHistory].[Id]={1} ",
                prId, historyId);

            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr, Common.PatientPrescriptionSP_GetPatientPrescriptionHistory, condStr) 
                as IDataReader;
            return CBO.FillObject<PrescriptionHistoryInfo>(reader);
        }

        public List<PrescriptionHistoryInfo> GetPrescriptionHistoryList(int prId, DateTime startDate, DateTime endDate)
        {
            string condStr = string.Format(@" [PatientPrescriptionHistory].[PRId]={0} AND [PatientPrescriptionHistory].[StartDate] >= '{1}' " +
              " AND [PatientPrescriptionHistory].[StartDate] <= '{2}' ", prId, startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"));

            string orderStr = " [PatientPrescriptionHistory].[PrescriptionId] asc, [PatientPrescriptionHistory].[StartDate] asc ";

            IDataReader dr = SqlHelper.ExecuteReader(Common.ConnStr,Common.PatientPrescriptionSP_GetPatientPrescriptionHistory, condStr, orderStr);

            return CBO.FillCollection<PrescriptionHistoryInfo>(dr);
        }

        public List<int> GetIdsOfUserWhoDosePrescription(int pid)
        {
            string condStr = string.Format(@" [PrescriptionId]={0} OR [ParentPrescriptionId]={0}", pid);
            IDataReader dr = SqlHelper.ExecuteReader(Common.ConnStr, Common.PatientPrescriptionSP_GetPatientCurrentPrescription, condStr)
                        as IDataReader;
            List<PrescriptionHistoryInfo> histories = CBO.FillCollection<PrescriptionHistoryInfo>(dr);

            List<int> userIds = new List<int>();
            for (int index = 0; index < histories.Count; index++)
            {
                userIds.Add(histories[index].UserId);
            }

            return userIds;
        }

        #endregion

        #region Private Methods

        private PrescriptionHistoryInfo GetPrescriptionHistory(SqlTransaction ts, int prId, int historyId)
        {
            string condStr = string.Format(@" [PatientPrescriptionHistory].[PRId]={0} "
                    + "AND [PatientPrescriptionHistory].[Id]={1}", prId, historyId);

            IDataReader reader = SqlHelper.ExecuteReader(ts, Common.PatientPrescriptionSP_GetPatientPrescriptionHistory, condStr) 
                as IDataReader;
            return CBO.FillObject<PrescriptionHistoryInfo>(reader);
        }   // GetPrescriptionHistory

        private int GetPrescriptionHistory(SqlTransaction ts, int prId, int prescritionId, DateTime startDate)
        {
            string condStr = string.Format(@" [PatientPrescriptionHistory].[PRId]={0} AND [PatientPrescriptionHistory].[PrescriptionId]={1} "
                    +" AND PatientPrescriptionHistory].[StartDate]={2}", prId, prescritionId, startDate);

            IDataReader reader = SqlHelper.ExecuteReader(ts, Common.PatientPrescriptionSP_GetPatientPrescriptionHistory, condStr) 
                as IDataReader;
            PrescriptionHistoryInfo info = CBO.FillObject<PrescriptionHistoryInfo>(reader);

            return info.Id;
        }   // GetPrescriptionHistory

        private int AddPrescriptionHistory(SqlTransaction ts, PrescriptionHistoryInfo info)
        {
            SqlParameter[] outParms = new SqlParameter[]  {
                new SqlParameter("@UserId", SqlDbType.Int),
                new SqlParameter("@PRId", SqlDbType.Int),
                new SqlParameter("@PrescriptionId", SqlDbType.Int),
                new SqlParameter("@ParentPrescriptionId", SqlDbType.Int),
                new SqlParameter("@State", SqlDbType.Int),
                new SqlParameter("@StartDate", SqlDbType.DateTime),
                new SqlParameter("@EndDate", SqlDbType.DateTime),
                new SqlParameter("@StopReason", SqlDbType.Int),
                new SqlParameter("@UsePurpose", SqlDbType.Int),
                new SqlParameter("@UpdateDate", SqlDbType.DateTime),
                new SqlParameter("@Id", SqlDbType.Int)
            };
            outParms[0].Value = info.UserId;
            outParms[1].Value = info.PRId;
            outParms[2].Value = info.PrescriptionId;
            outParms[3].Value = info.ParentPrescriptionId;
            outParms[4].Value = info.State;
            outParms[5].Value = info.StartDate;
            outParms[6].Value = info.EndDate;
            outParms[7].Value = info.StopReason;
            outParms[8].Value = info.UsePurpose;
            outParms[9].Value = info.UpdateDate;
            outParms[10].Direction = ParameterDirection.Output;

            SqlHelper.ExecuteNonQuery(ts, CommandType.StoredProcedure, Common.PatientPrescriptionSP_AddPatientPrescriptionHistory, outParms);

            return Convert.ToInt32(outParms[10].Value.ToString());
        }   // AddPrescriptionHistory


        private int UpdatePrescriptionHistory(SqlTransaction ts, PrescriptionHistoryInfo info)
        {
            return SqlHelper.ExecuteNonQuery(ts,
                Common.PatientPrescriptionSP_UpdatePatientPrescriptionHistory,
                info.Id,
                info.UserId,
                info.PRId,
                info.PrescriptionId,
                info.ParentPrescriptionId,
                info.State,
                info.StartDate,
                info.EndDate,
                info.StopReason,
                info.UsePurpose,
                info.UpdateDate);
        }   // UpdatePrescriptionHistory

        private bool DelPrescriptionHistory(SqlTransaction ts, int prId, int historyId)
        {
            string condStr = string.Format(@" [PatientPrescriptionHistory].[PRId]={0} AND [PatientPrescriptionHistory].[Id]={1}", prId, historyId);

            return SqlHelper.ExecuteNonQuery(ts, Common.PatientPrescriptionSP_DeletePatientPrescriptionHistory, condStr.ToString()) > 0;
        }   // DelPrescriptionHistory

        private PrescriptionHistoryInfo GetCurrentPrescription(SqlTransaction ts, int prId, int historyId)
        {
            string condStr = string.Format(@" [PatientCurrentPrescription].[PRId]={0} "
                + "AND [PatientCurrentPrescription].[PrescriptionHistoryId]={1}", prId, historyId);

            IDataReader reader = SqlHelper.ExecuteReader(ts,Common.PatientPrescriptionSP_GetPatientCurrentPrescription, condStr) 
                as IDataReader;
            return CBO.FillObject<PrescriptionHistoryInfo>(reader);
        }   // GetCurrentPrescription

        private int AddCurrentPrescription(SqlTransaction ts, PrescriptionHistoryInfo info)
        {
            SqlParameter[] outParams = new SqlParameter[] {
                new SqlParameter("@UserId", SqlDbType.Int),
                new SqlParameter("@PrId", SqlDbType.Int),
                new SqlParameter("@PrescriptionId", SqlDbType.Int),
                new SqlParameter("@ParentPrescriptionId", SqlDbType.Int),
                new SqlParameter("@IsUsing", SqlDbType.Int),
                new SqlParameter("@PrescriptionHistoryId", SqlDbType.Int),
                new SqlParameter("@CreateTime", SqlDbType.DateTime),
                new SqlParameter("@Id", SqlDbType.Int)
            };
            outParams[0].Value = info.UserId;
            outParams[1].Value = info.PRId;
            outParams[2].Value = info.PrescriptionId;
            outParams[3].Value = info.ParentPrescriptionId;
            outParams[4].Value = info.IsUsing;
            outParams[5].Value = info.PrescriptionHistoryId;
            outParams[6].Value = DateTime.Now;
            outParams[7].Direction = ParameterDirection.Output;

            SqlHelper.ExecuteNonQuery(ts, CommandType.StoredProcedure, Common.PatientPrescriptionSP_AddPatientCurrentPrescription, outParams);

            return Convert.ToInt32(outParams[7].Value.ToString());
        }   // AddCurrentPrescription

        private int UpdateCurrentPrescription(SqlTransaction ts, PrescriptionHistoryInfo info)
        {
            return SqlHelper.ExecuteNonQuery(ts,
                Common.PatientPrescriptionSP_UpdatePatientCurrentPrescription,
                info.Id,
                info.UserId,
                info.PRId,
                info.PrescriptionId,
                info.ParentPrescriptionId,
                info.IsUsing,
                info.PrescriptionHistoryId,
                DateTime.Now);
        }   // UpdateCurrentPrescription

        private int DelCurrentPrescription(SqlTransaction ts, int prId, int prescriptionId)
        {
            string condStr = string.Format(@" [PatientCurrentPrescription].[PRId]={0} AND [PatientCurrentPrescription].[PrescriptionId]={1}",
                prId, prescriptionId);

            return SqlHelper.ExecuteNonQuery(ts,Common.PatientPrescriptionSP_DeletePatientCurrentPrescription, condStr.ToString());
        }   // DelCurrentPrescription

        // 判断当前纪录表中是否已存在纪录
        private bool CheckExsitCurrentPrescription(SqlTransaction ts, int prId, int prescriptionId)
        {
            bool retVal = false;

            string condStr = string.Format(@" [PatientCurrentPrescription].[PRId]={0} AND [PatientCurrentPrescription].[PrescriptionId]={1} ",
                prId, prescriptionId);

            IDataReader dr = SqlHelper.ExecuteReader(ts, Common.PatientPrescriptionSP_GetCurrentPrescriptionCount, condStr);

            if (dr != null && dr.Read())
            {
                if (dr.GetInt32(0) > 0)
                {
                    retVal = true;
                }
                dr.Close();
            }

            return retVal;
        }   // CheckExsitCurrentPrescription

        // 判断当前纪录表中是否已存在纪录
        private bool CheckExsitCurrentPrescriptionByHistoryId(SqlTransaction ts, int prId, int historyId)
        {
            bool retVal = false;

            string condStr = string.Format(@" [PatientCurrentPrescription].[PRId]={0} AND [PatientCurrentPrescription].[PrescriptionHistoryId]={1} ",
                prId, historyId);

            IDataReader dr = SqlHelper.ExecuteReader(ts, Common.PatientPrescriptionSP_GetCurrentPrescriptionCount, condStr);

            if (dr != null && dr.Read())
            {
                if (dr.GetInt32(0) > 0)
                {
                    retVal = true;
                }
                dr.Close();
            }

            return retVal;
        }   // CheckExsitCurrentPrescriptionByHistoryId

        #endregion
    }
}
